﻿using Dapper; 
using SwaySway.Dao.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WindowsFormsApp1.Mode;

namespace WindowsFormsApp1.DAL
{
	 public class ProductDAL
	{
		/// <summary>
		/// 增加一条数据,返回ID
		/// </summary>
		public static int Add(Prouduct model)
		{
			DepperSqlHelp<Prouduct> help = new DepperSqlHelp<Prouduct>();
			int result = help.InsertReturn(model, "Prouduct3");
			return result;
		}
		/// <summary>
		/// 获得数据列表
		/// </summary>
		public static DataTable GetList(string strWhere,string orderby ,string table)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select top 10* ");
			strSql.Append(" FROM  "+ table);
			if (strWhere.Trim() != "")
			{
				strSql.Append(" where " + strWhere);
			}
			if (orderby.Trim() != "")
			{
				strSql.Append("  order by " + orderby);
			}
			using (var conn = SqlConn.OpenConnectionStar())
			{
				SqlCommand cmd = new SqlCommand(strSql.ToString(), conn);
				SqlDataAdapter da = new SqlDataAdapter(cmd);
				DataSet ds = new DataSet();
				da.Fill(ds, "Prouduct3");
				return ds.Tables[0];
			}
		} 
		/// <summary>
		/// 获取记录总数
		/// </summary>
		public static int GetRecordCount(string strWhere)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select count(1) FROM Prouduct3");
			if (strWhere.Trim() != "")
			{
				strSql.Append(" where " + strWhere);
			}
			using (IDbConnection conn = SqlConn.OpenConnectionStar())
			{
				var model = conn.Query<int>(strSql.ToString(), null, commandType: CommandType.Text).FirstOrDefault();
				return model;
			}
		}

		/// <summary>
		/// 获取一级类别
		/// </summary>
		public static DataTable GetOneClass()
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select CategoryClassID from Prouduct3 group by CategoryClassID ");
			using (var conn = SqlConn.OpenConnectionStar())
			{
				SqlCommand cmd = new SqlCommand(strSql.ToString(), conn);
				SqlDataAdapter da = new SqlDataAdapter(cmd);
				DataSet ds = new DataSet();
				da.Fill(ds,"pro");
				return ds.Tables[0];
			}
		}


		static DepperSqlHelp<Prouduct> dapphelper = new DepperSqlHelp<Prouduct>();


		public static EntityPage<Prouduct> GetProuductList(BaseSearchPage search)
		{
			
			StringBuilder SqlStr = new StringBuilder();
			SqlStr.AppendFormat("Prouduct3 where 1=1");
			if (!string.IsNullOrEmpty(search.isZy) && search.isZy!="-1")
			{
			 	SqlStr.AppendFormat(" and  iszy='{0}'", search.isZy);
			}
			
			EntityPage<Prouduct> result = dapphelper.Pagination<Prouduct>(SqlStr.ToString(), search.CurrentIndex, search.PageSize, true, "[ID]   ,PastTimesCount ,PComment ,isZy ,[CategoryClassID]  ,PreferentialProportion    ,[Class1]      ,[Class2]      ,[Class3]      ,[PName]      ,[PDescribe]      ,[Price]      ,[OriginalPrice]      ,[PUrl]      ,[SaleCount]      ,[PicUrl1]      ,[PicUrl2]      ,[PicUrl3]      ,[PicUrl4]      ,[PicUrl5]      ,[PicUrl6]      ,[PicUrl7]      ,[PScore] ", search.orderByWhere, null);
			return result;
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public static List<Prouduct> GetListByPage(string strWhere, string orderby, int startIndex, int endIndex, ref int count)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby);
			}
			else
			{
				strSql.Append("order by T.Rid desc");
			}
			strSql.Append(")AS Row, T.*  from Prouduct3 T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			using (IDbConnection conn = SqlConn.OpenConnectionStar())
			{
				var model = conn.Query<Prouduct>(strSql.ToString(), null, commandType: CommandType.Text).ToList();
				return model;
			}
		}
	}
}
